package com.ews.onlineexchange.service;
import java.util.ArrayList;
import java.util.List;

import javax.enterprise.context.ConversationScoped;
import javax.inject.Inject;
import javax.inject.Named;
import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Subquery;

import org.apache.commons.collections.CollectionUtils;
import com.ews.onlineexchange.model.QualificationCert;
import com.ews.onlineexchange.model.QualificationLevel;
import com.ews.onlineexchange.model.QualificationTemplate;
import com.ews.onlineexchange.model.Sector;
import com.ews.onlineexchange.model.ServiceSupplier;

@Named
@ConversationScoped
public class SupplierSelector extends EntityQuery<ServiceSupplier> {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	
	private boolean dirty;
	private QualificationTemplate qualificationTemplate = new QualificationTemplate();
	
	public SupplierSelector() {
	
	}

	@Override
	protected List<Predicate> getRestrictions(CriteriaQuery cq, CriteriaBuilder cb, Root<ServiceSupplier> entity) {		
		List<Predicate> restrictions = new ArrayList<Predicate>();
		restrictions.add(cb.isTrue(entity.<Boolean>get("valid")));
		Join<ServiceSupplier,QualificationCert>  certs = entity.join("certs");
		restrictions.add(cb.isTrue(certs.<Boolean>get("valid")));
		restrictions.add(cb.isTrue(certs.<Boolean>get("auditResult")));
		List<Sector> sectors = getQualificationTemplate().getSectors();
		for (Sector sector : sectors) {			
			Subquery<String> subQuery = cq.subquery(String.class);
			Root<ServiceSupplier> validityId = subQuery.from(ServiceSupplier.class);
			Join<QualificationCert, QualificationTemplate> quaTemplate = validityId.joinList("certs").join("qualificationTemplates");
			Join<QualificationTemplate, QualificationLevel> quaLevel = quaTemplate.join("qualificationLevel");
			Join<QualificationTemplate, Sector> joinSector = quaTemplate.join("sectors");
			subQuery.select(validityId.get("id"));
			Predicate preReq = cb.and(new Predicate[] {
					cb.equal(joinSector.get("id"), sector.getId()),
					cb.equal(quaTemplate.get("qualificationClass"), qualificationTemplate.getQualificationClass()),
					cb.lessThanOrEqualTo(quaLevel.<Integer>get("value"), qualificationTemplate.getQualificationLevel().getValue())
			});
			subQuery.where(preReq);
			restrictions.add(cb.in(entity.get("id")).value(subQuery));
		}
		
		return restrictions;
	}

	public boolean isDirty() {
		return dirty;
	}

	public void setDirty(boolean dirty) {
		this.dirty = dirty;
	}

	public QualificationTemplate getQualificationTemplate() {
		return qualificationTemplate;
	}

	public void setQualificationTemplate(QualificationTemplate qualificationTemplate) {
		this.qualificationTemplate = qualificationTemplate;
	}

	@Override
	protected boolean isDistinct() {
		return true;
	}
	
	private String SERVICESUPPLIER_LIST_BY_SECTOR = " "+
					" SELECT distinct * from servicesupplier supplier  " +
					" INNER JOIN system_user u on u.id  = supplier.id "+
					" INNER JOIN qualificationcert cert on cert.servicesupplier_id = supplier.id " +
					" INNER JOIN Cert_Templates cert_temp on cert.id = cert_temp.qualificationcert_id " + 
					" INNER JOIN QualificationTemplate qualificationtemplate  ON qualificationtemplate.id = cert_temp.qualificationtemplates_id " + 
					" INNER JOIN QualificationLevel qualificationlevel  ON qualificationtemplate.qualificationlevel_id = qualificationlevel.id " + 
					" INNER JOIN Qualificationtemplate_Sector temp_sector  ON qualificationtemplate.id = temp_sector.qualificationtemplate_id " +
					" INNER JOIN Sector sector  ON sector.id = temp_sector.sectors_id " +
					" WHERE supplier.valid = 1 " +
					" AND 	u.auditted = 1 " +
					" AND cert.auditResult = 1 " +
					" AND cert.valid = 1 " +
					" AND sector.id = ? " +
					" AND qualificationtemplate.qualificationclass_id = ? " +
					" AND qualificationlevel.value <= ? ";
	@Inject
	EntityManager entityManager;
	public List<ServiceSupplier> getSupplierBySector(QualificationTemplate template,Sector sector){

			List<ServiceSupplier> suppliers = entityManager.createNativeQuery(SERVICESUPPLIER_LIST_BY_SECTOR, ServiceSupplier.class)
												.setParameter(1, sector.getId())
												.setParameter(2,template.getQualificationClass().getId())
												.setParameter(3, template.getQualificationLevel().getValue())
												.getResultList();
			return suppliers;
			
	}
	
	public List<ServiceSupplier> getSupplierByQualificationTemplate(QualificationTemplate qualificationTemplate){
		List<Sector> sectors = qualificationTemplate.getSectors();
		List<ServiceSupplier> suppliers = new ArrayList<>(); 
		for (Sector sector : sectors) {		
			List<ServiceSupplier> tempSuppliers = getSupplierBySector(qualificationTemplate,sector);
			if (suppliers.isEmpty()) {
				suppliers = tempSuppliers;
			} else 
				suppliers = (List<ServiceSupplier>) CollectionUtils.retainAll(suppliers, tempSuppliers);
		}
		
		return suppliers;
	}
	

	
}
